﻿/*
 * 文件名：User.cs
 * 类名：XHCTMS.DAL.User
 * 创建人：杨学鹏
 * 创建时间：2013-5-21
 * 
 * 修改人：张夏
 * 修改时间：2013-7-2
 * 修改内容： 添加方法GetUserInfo
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace XHCTMS.DAL
{
    /// <summary>
    /// 对用户的操作
    /// </summary>
    public class User
    {
        /// <summary>
        /// 增加一个用户
        /// </summary>
        /// <param name="User">用户信息</param>
        /// <returns>返回int类型 1/成功, 0/失败, -1/异常</returns>
        public int Add(MODEL.User User)
        {
            string SqlStatement = @"insert into [user] 
            values(@user_name,'666666',@user_dep_id,@user_job_Id,@user_title,@user_time,default)";

            SqlParameter[] Para = {
                                      new SqlParameter("@user_name",SqlDbType.NVarChar,32),
                                      new SqlParameter("@user_dep_id",SqlDbType.Int),
                                      new SqlParameter("@user_job_Id",SqlDbType.Int),
                                      new SqlParameter("@user_title",SqlDbType.NVarChar,16),
                                      new SqlParameter("@user_time",SqlDbType.NVarChar,32)
                                  };

            Para[0].Value = User.UserName;
            Para[1].Value = User.UserDepId;
            Para[2].Value = User.UserJobId;
            Para[3].Value = User.UserTitle;
            Para[4].Value = User.UserTime;

            return new DBHELP.Common().ExcuteQuery(SqlStatement, Para);
        }

        /// <summary>
        /// 删除一个用户
        /// </summary>
        /// <param name="UserId">用户号</param>
        /// <param name="JobStatus">用户在职状态 1/在职 0/离职</param>
        /// <returns>返回int类型 1/成功, 0/失败, -1/异常</returns>
        public int Delete(int UserId,byte JobStatus)
        {
            string SqlStatement = "update [user] set [user].user_flag=@job_status where [user].[user_id]=@user_id";

            SqlParameter[] Para = {
                                        new SqlParameter("@user_id", SqlDbType.Int),
                                        new SqlParameter("@job_status",SqlDbType.Bit)
                                  };

            Para[0].Value = UserId;
            Para[1].Value = JobStatus;

            return new DBHELP.Common().ExcuteQuery(SqlStatement, Para);
        }

        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="User">用户信息</param>
        /// <returns>返回int类型 1/成功, 0/失败, -1/异常</returns>
        public int Modify(MODEL.User User)
        {
            string SqlStatement = @"update [user] set [user].[user_name]=@user_name,
            [user].user_dep_id=@user_dep_id,[user].user_job_Id=@user_job_Id,
            [user].user_title=@user_title,[user].user_time=@user_time where [user].[user_id]=@user_id";

            //[user].user_pwd=@user_pwd,
            SqlParameter[] Para = {
                                      new SqlParameter("@user_id",SqlDbType.Int),
                                      new SqlParameter("@user_name",SqlDbType.NVarChar,32),
                                      //new SqlParameter("@user_pwd",SqlDbType.NVarChar,32),
                                      new SqlParameter("@user_dep_id",SqlDbType.Int),
                                      new SqlParameter("@user_job_Id",SqlDbType.Int),
                                      new SqlParameter("@user_title",SqlDbType.NVarChar,16),
                                      new SqlParameter("@user_time",SqlDbType.NVarChar,32)
                                  };

            Para[0].Value = User.UserId;
            Para[1].Value = User.UserName;
            //Para[2].Value = User.UserPwd;
            Para[2].Value = User.UserDepId;
            Para[3].Value = User.UserJobId;
            Para[4].Value = User.UserTitle;
            Para[5].Value = User.UserTime;

            return new DBHELP.Common().ExcuteQuery(SqlStatement, Para);
        }

        /// <summary>
        /// 按条件查询用户信息并分页
        /// </summary>
        /// <param name="PageIndex">第N页</param>
        /// <param name="PageSize">每页多少条</param>
        /// <param name="QueryString">查询条件</param>
        /// <param name="">查询的用户是否在职</param>
        /// <returns>返回DataTable类型  null/异常</returns>
        public DataTable View(int PageIndex=1, int PageSize=50, string QueryString="",byte flag=1,string columnname="",string SortSquareTest="")
        {
            StringBuilder SqlStatement = new StringBuilder(@"select top (@pagesize) [user].[user_id],[user].[user_name],
            department.dep_name,job.job_name,[user].user_title,[user].user_time from 
            [user],department,Job where job.job_id=[user].user_job_Id and 
            department.dep_id = [user].user_dep_id and [user].user_flag=@user_flag and [user].[user_id] not in
            (select top (@pagesize*(@pageindex-1)) [user].[user_id] from [user],department,Job where 
            department.dep_id = [user].user_dep_id and job.job_id=[user].user_job_Id and [user].user_flag=@user_flag and 
            ([user].[user_id] like '%'+@Query+'%' or [user].[user_name] like '%'+@Query+'%' or 
            department.dep_name like '%'+@Query+'%' or job.job_name like '%'+@Query+'%' or 
            [user].user_title like '%'+@Query+'%' or [user].user_time like '%'+@Query+'%') ");
            GetSortSquareTest(columnname, SortSquareTest, SqlStatement);
            SqlStatement.Append(@" )
            and ([user].[user_id] like '%'+@Query+'%' or [user].[user_name] like '%'+@Query+'%' or 
            department.dep_name like '%'+@Query+'%' or job.job_name like '%'+@Query+'%' or 
            [user].user_title like '%'+@Query+'%' or [user].user_time like '%'+@Query+'%') ");
            GetSortSquareTest(columnname, SortSquareTest, SqlStatement);

            SqlParameter[] Para ={
                                     new SqlParameter("@pageindex",SqlDbType.Int),
                                     new SqlParameter("@pagesize",SqlDbType.Int),
                                     new SqlParameter("@Query",SqlDbType.VarChar,32),
                                     new SqlParameter("@user_flag",SqlDbType.Bit)
                                };

            Para[0].Value = PageIndex.ToString();
            Para[1].Value = PageSize.ToString();
            Para[2].Value = QueryString;
            Para[3].Value = flag;

            return new DBHELP.Common().GetDataTable(SqlStatement.ToString(), Para);
        }

        private static void GetSortSquareTest(string columnname, string SortSquareTest, StringBuilder SqlStatement)
        {
            switch (columnname)
            {
                case "user_id": SqlStatement.Append("order by [user].[user_id] ");
                    break;
                case "user_name": SqlStatement.Append("order by [user].user_name ");
                    break;
                case "dep_name": SqlStatement.Append("order by department.dep_name ");
                    break;
                case "job_name": SqlStatement.Append("order by job.job_name ");
                    break;
                case "user_title": SqlStatement.Append("order by [user].user_title ");
                    break;
                case "user_time": SqlStatement.Append("order by [user].user_time ");
                    break;
            }
            if (SortSquareTest != "")
            {
                SqlStatement.Append(" desc");
            }
            else
            {
                SqlStatement.Append(" asc");
            }
        }


        /// <summary>
        /// 取得跟条件相关信息的总条数
        /// </summary>
        /// <param name="QueryString">查询条件</param>
        /// <returns>返回DataTable类型  null/异常</returns>
        public DataTable NumberOfMessages(string QueryString)
        {
            string SqlStatement = @"select COUNT(user_id)  from [user],department,Job where 
            department.dep_id=[user].user_dep_id and job.job_id=[user].user_job_Id
            and [user].user_flag=1 and ([user].[user_id] like '%'+@Query+'%' or [user].[user_name] like '%'+@Query+'%' or 
            department.dep_name like '%'+@Query+'%' or job.job_name like '%'+@Query+'%' or 
            [user].user_title like '%'+@Query+'%' or [user].user_time like '%'+@Query+'%') and [user].user_flag=1 ";
            SqlParameter[] Para = { new SqlParameter("@Query", SqlDbType.VarChar, 32) };

            Para[0].Value = QueryString;

            return new DBHELP.Common().GetDataTable(SqlStatement, Para);
        }

        /// <summary>
        /// 获取部门信息
        /// </summary>
        /// <returns>返回DataTable类型  null/异常</returns>
        public DataTable DepView()
        {
            string SqlStatement = "select department.dep_id,department.dep_name from department where department.dep_flag=1";

            return new DBHELP.Common().GetDataTable(SqlStatement);
        }

        /// <summary>
        /// 获取岗位信息
        /// </summary>
        /// <returns>返回DataTable类型  null/异常</returns>
        public DataTable JobView()
        {
            string SqlStatement = "select Job.job_id,Job.job_name from Job where Job.job_flag=1";

            return new DBHELP.Common().GetDataTable(SqlStatement);
        }

        /// <summary>
        /// 获得老师姓名
        /// </summary>
        /// <returns>返回DataTable类型  null/异常</returns>
        public DataTable GetUserName()
        {
            string SqlStatement = "select [user_id],[user_name] from [user] where user_flag =1 order by [user_name] ";
            return new DBHELP.Common().GetDataTable(SqlStatement);
        }

        /// <summary>
        /// 查询用户名是否存在
        /// </summary>
        /// <param name="name">用户名</param>
        /// <returns>返回string null/不存在 否则存</returns>
        public string FindUserName(string name)
        {
            string SqlStatement = "select [user].[user_name] from [user] where [user].[user_name]=@name";
            SqlParameter[] Para = { new SqlParameter("@name", SqlDbType.NVarChar, 32) };
            Para[0].Value = name;
            return new DBHELP.Common().GetDataScalar(SqlStatement,Para);
        }



 


        /// <summary>
        /// 根据userName,userPwd 获取用户信息
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataTable GetUserInfo(string userName, string userPwd)
        {
            string strSql = @"select tu.[user_id],tu.[user_name],tj.job_name,tu.user_title,td.dep_name from [user] tu 
left join job tj  on tu.user_job_Id = tj.job_id
left join department  td on tu.user_dep_id = td.dep_id where user_name =@userName and user_pwd =@userPwd";
            SqlParameter[] paras = { new SqlParameter("@userName", userName) ,
                                   new SqlParameter("@userPwd",userPwd)};
            return new DBHELP.Common().GetDataTable(strSql, paras);
        }


    }
}
